<?php
ob_start();
header("Content-type:text/html;charset=utf-8");
ini_set('memory_limit','88M');
ini_set('max_execution_time','0');
date_default_timezone_set('Asia/Shanghai');
error_reporting(0);

include 'config.php';

/*----------链接mysql数据库  start------------*/
$conn = @mysql_connect($db_host,$db_user,$db_pwd) or die('mysql server failed!!');
@mysql_query('set names utf8',$conn);
@mysql_select_db($db_name,$conn) or die('can not find mysql dbname:'.$db_name);
/*----------链接mysql数据库  end------------*/

/*----------常用方法封装  start------------*/
function querys($sql){
	return @mysql_query($sql);
}

function getall($sql){
	$query = querys($sql);
	$list = '';
	while($row = @mysql_fetch_assoc($query)){
		$list[] = $row;
	}
	return $list;
}

function getone($sql){
	$query = querys($sql);
	return @mysql_fetch_assoc($query);
}
/*
*修改实例
*$table=表名
*$err=数组
*$id=编号
*/
function edit_update_cl($table,$err,$id){
	$sql = "UPDATE ".$table." SET ";
	foreach ( $err as $key => $value  ){
		if(preg_match("/^\d*$/",$key)){
			#过滤数字
		}else{
			$sql .= $key."='".addslashes($value)."', ";
		}
	}
	$sql = substr($sql, 0, strlen($sql)-2) .  " WHERE id=".$id;

	@mysql_query($sql) or die (mysql_error());

}

/*
*插入实例
*$table=表名
*$err=数组
*/
function add_insert_cl($table,$err){
	$sql = "INSERT INTO ".$table." (";
	foreach ( $err as $key => $value  ){
		$sql .= $key.", ";
	}
	$sql = substr($sql, 0, strlen($sql)-2) .  ") VALUES (";
	foreach ( $err as $key => $value  ){
		$sql .= "'".addslashes($value)."', ";
	}
	$sql = substr($sql, 0, strlen($sql)-2) .  ")";
	@mysql_query($sql) or die (mysql_error());
	return mysql_insert_id();
}
require   'phpexcel/Classes/PHPExcel.php';
 function user_daochu_jb(){
	 // 实例化excel类
	 $objPHPExcel = new PHPExcel();
	 // 操作第一个工作表
	 $objPHPExcel->setActiveSheetIndex(0);
	 // 设置sheet名
	 $objPHPExcel->getActiveSheet()->setTitle('xx列表');

	 // 设置表格宽度
	 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
	 $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
	 $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
	 $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
	 $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
	 $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(5);
	 $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);


	 // 列名表头文字加粗
	 $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFont()->setBold(true);
	 // 列表头文字居中
	 $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getAlignment()
		 ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

	 // 列名赋值
	 $objPHPExcel->getActiveSheet()->setCellValue('A1', '会员编号');
	 $objPHPExcel->getActiveSheet()->setCellValue('B1', '会员姓名');
	 $objPHPExcel->getActiveSheet()->setCellValue('C1', '会员等级');
	 $objPHPExcel->getActiveSheet()->setCellValue('D1', '团队等级');
	 $objPHPExcel->getActiveSheet()->setCellValue('E1', '联系电话');
	 $objPHPExcel->getActiveSheet()->setCellValue('F1', '推荐人');
	 $objPHPExcel->getActiveSheet()->setCellValue('G1', '服务中心');
	 $objPHPExcel->getActiveSheet()->setCellValue('H1', '余额');
	 $objPHPExcel->getActiveSheet()->setCellValue('I1', '报单币');
	 $objPHPExcel->getActiveSheet()->setCellValue('J1', '推荐人数');
	 $objPHPExcel->getActiveSheet()->setCellValue('K1', '状态');
	 $objPHPExcel->getActiveSheet()->setCellValue('L1', '锁定状态');
	 $objPHPExcel->getActiveSheet()->setCellValue('M1', '注册时间');
	 $objPHPExcel->getActiveSheet()->setCellValue('N1', '激活时间');
/*	 $res=db('users')
		 ->field('nickname,username,tlevel,mobile,up_userid,center_id,money_jifen,money_baodan,recount,activation,is_lock,reg_time,jihuo_time')
		 ->where('id','gt',0)
		 ->select();*/
	 $res=getall("select nickname,username,tlevel,mobile,up_userid,center_id,money_jifen,money_baodan,recount,activation,is_lock,reg_time,jihuo_time from clt_users where id>0 order by id asc");

	 // 数据起始行
	 $row_num = 2;
	 // 向每行单元格插入数据
	 foreach($res as $value)
	 {
		 // 设置所有垂直居中
		 $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'J' . $row_num)->getAlignment()
			 ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
		 // 设置价格为数字格式
		 $objPHPExcel->getActiveSheet()->getStyle('D' . $row_num)->getNumberFormat()
			 ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
		 // 居中
		 $objPHPExcel->getActiveSheet()->getStyle('E' . $row_num . ':' . 'H' . $row_num)->getAlignment()
			 ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

		 // 设置单元格数值
		 $objPHPExcel->getActiveSheet()->setCellValue('A' . $row_num, $value['nickname']);
		 $objPHPExcel->getActiveSheet()->setCellValue('B' . $row_num, $value['username']);
		 $objPHPExcel->getActiveSheet()->setCellValue('C' . $row_num, "普通客户");
		 $lname=getone("select level_name from clt_user_tlevel where `level`=".$value['tlevel']);
		 $objPHPExcel->getActiveSheet()->setCellValue('D' . $row_num, $lname['level_name']);
		 $objPHPExcel->getActiveSheet()->setCellValue('E' . $row_num, $value['mobile']);
		 $nickname=getone("select nickname from clt_users where id=".$value['up_userid']);
		 $objPHPExcel->getActiveSheet()->setCellValue('F' . $row_num, $nickname['nickname']);
		 $center=getone("select nickname from clt_users where id=".$value['center_id']);
		 $objPHPExcel->getActiveSheet()->setCellValue('G' . $row_num, $center['nickname']);
		 $objPHPExcel->getActiveSheet()->setCellValue('H' . $row_num, $value['money_jifen']);
		 $objPHPExcel->getActiveSheet()->setCellValue('I' . $row_num, $value['money_baodan']);
		 $objPHPExcel->getActiveSheet()->setCellValue('J' . $row_num, $value['recount']);
		 $objPHPExcel->getActiveSheet()->setCellValue('K' . $row_num, $value['activation']?"已激活":"未激活");
		 $objPHPExcel->getActiveSheet()->setCellValue('L' . $row_num, $value['is_lock']?"已锁定":"未锁定");
		 $objPHPExcel->getActiveSheet()->setCellValue('M' . $row_num, date('Y-m-d h:i:s',$value['reg_time']));
		 $objPHPExcel->getActiveSheet()->setCellValue('N' . $row_num, date('Y-m-d h:i:s',$value['jihuo_time']));
		 $row_num++;
	 }

	 $outputFileName = 'users_' . time() . '.xls';
	 $xlsWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
	 ob_end_clean();
	 header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
	 header("Content-Type: application/force-download");
	 header("Content-Type: application/octet-stream");
	 header("Content-Type: application/download");
	 header('Content-Disposition:inline;filename="' . $outputFileName . '"');
	 header("Content-Transfer-Encoding: binary");
	 header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
	 header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
	 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
	 header("Pragma: no-cache");
	 $xlsWriter->save("php://output");
	 echo file_get_contents($outputFileName);
	/*set_time_limit (0);
	header("Content-type:application/vnd.ms-excel");
	header("Content-Disposition:filename=table.xls");
	$str="会员编号\t会员姓名\t会员等级\t团队等级\t联系电话\t推荐人\t服务中心\t余额\t报单币\t推荐人数\t状态\t锁定状态\t注册时间\t激活时间\t\n";
	echo $str;
	$list=getall("select nickname,username,tlevel,mobile,up_userid,center_id,money_jifen,money_baodan,recount,activation,is_lock,reg_time,jihuo_time from clt_users where id>0");
	foreach($list as $key=>$row){
		echo $row['nickname']."\t";
		echo $row['username']."\t";
		echo "普通客户\t";
		$lname=db('user_tlevel')->where('level',$row['tlevel'])->value('level_name');
		echo "'".$lname."'"."\t";
		echo $row['mobile']."\t";
		$nickname=db('users')->where('id',$row['up_userid'])->value('nickname');
		echo $nickname."\t";
		$center_id=db('users')->where('id',$row['center_id'])->value('nickname');
		echo $center_id."\t";

		echo $row['money_jifen']."\t";
		echo $row['money_baodan']."\t";
		echo $row['recount']."\t";

		if ($row['activation']==1){
			echo "已激活\t";
		}elseif($row['activation']==0){
			echo "未激活\t";
		}
		if ($row['is_lock']==1){
			echo "已锁定\t";
		}elseif($row['is_lock']==0){
			echo "未锁定\t";
		}
		echo date('Y-m-d H:i:s',$row['reg_time'])."\t";
		echo date('Y-m-d H:i:s',$row['jihuo_time'])."\t\n";
	}
	exit;*/



}
function money_log_daochu($key){
	 // 实例化excel类
	 $objPHPExcel = new PHPExcel();
	 // 操作第一个工作表
	 $objPHPExcel->setActiveSheetIndex(0);
	 // 设置sheet名
	 $objPHPExcel->getActiveSheet()->setTitle('xx列表');

	 // 设置表格宽度
	 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
	 $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
	 $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
	 $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
	 $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
	 $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(5);
	 $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);


	 // 列名表头文字加粗
	 $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFont()->setBold(true);
	 // 列表头文字居中
	 $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getAlignment()
		 ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

	 // 列名赋值
	 $objPHPExcel->getActiveSheet()->setCellValue('A1', '会员编号');
	 $objPHPExcel->getActiveSheet()->setCellValue('B1', '会员手机号');
	 $objPHPExcel->getActiveSheet()->setCellValue('C1', '会员姓名');
	 $objPHPExcel->getActiveSheet()->setCellValue('D1', '变动类型');
	 $objPHPExcel->getActiveSheet()->setCellValue('E1', '资金类型');
	 $objPHPExcel->getActiveSheet()->setCellValue('F1', '金额变动');
	 $objPHPExcel->getActiveSheet()->setCellValue('G1', '当前余额');
	 $objPHPExcel->getActiveSheet()->setCellValue('H1', '时间');
	 $objPHPExcel->getActiveSheet()->setCellValue('I1', '备注');
/*	 $res=db('users')
		 ->field('nickname,username,tlevel,mobile,up_userid,center_id,money_jifen,money_baodan,recount,activation,is_lock,reg_time,jihuo_time')
		 ->where('id','gt',0)
		 ->select();*/
	$res=getall("select m.* from clt_money_log as m join  clt_users as u  on m.userid=u.id where m.id >0 and (u.nickname like '%".$key."%' or u.mobile like '%".$key."%' or u.username like '%".$key."%' )");


	 // 数据起始行
	 $row_num = 2;
	 // 向每行单元格插入数据
	 foreach($res as $value)
	 {
		 // 设置所有垂直居中
		 $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'J' . $row_num)->getAlignment()
			 ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
		 // 设置价格为数字格式
		 $objPHPExcel->getActiveSheet()->getStyle('D' . $row_num)->getNumberFormat()
			 ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
		 // 居中
		 $objPHPExcel->getActiveSheet()->getStyle('E' . $row_num . ':' . 'H' . $row_num)->getAlignment()
			 ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

		 // 设置单元格数值
		 $center=getone("select nickname,mobile,username from clt_users where id=".$value['userid']);
		 $objPHPExcel->getActiveSheet()->setCellValue('A' . $row_num, $center['nickname']);
		 $objPHPExcel->getActiveSheet()->setCellValue('B' . $row_num, $center['mobile']);
		 $objPHPExcel->getActiveSheet()->setCellValue('C' . $row_num, $center['username']);
		 $lname=getone("select `type` from clt_money_type where `id`=".$value['source']);
		 $objPHPExcel->getActiveSheet()->setCellValue('D' . $row_num, $lname['type']);
		 $name=getone("select `name` from clt_money_config where id=".$value['type']);
		 $objPHPExcel->getActiveSheet()->setCellValue('E' . $row_num, $name['name']);
		 $objPHPExcel->getActiveSheet()->setCellValue('F' . $row_num, $value['num']);
		 $objPHPExcel->getActiveSheet()->setCellValue('G' . $row_num, $value['dangqian']);
		 $objPHPExcel->getActiveSheet()->setCellValue('H' . $row_num, date('Y-m-d h:i:s',$value['time']));
		 $objPHPExcel->getActiveSheet()->setCellValue('I' . $row_num, $value['beizhu']);
		 $row_num++;
	 }

	 $outputFileName = 'teacher_' . time() . '.xls';
	 $xlsWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
	 ob_end_clean();
	 header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
	 header("Content-Type: application/force-download");
	 header("Content-Type: application/octet-stream");
	 header("Content-Type: application/download");
	 header('Content-Disposition:inline;filename="' . $outputFileName . '"');
	 header("Content-Transfer-Encoding: binary");
	 header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
	 header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
	 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
	 header("Pragma: no-cache");
	 $xlsWriter->save("php://output");
	 echo file_get_contents($outputFileName);
}
function tixian_daochu($key,$time1,$time2){
	// 实例化excel类
	$objPHPExcel = new PHPExcel();
	// 操作第一个工作表
	$objPHPExcel->setActiveSheetIndex(0);
	// 设置sheet名
	$objPHPExcel->getActiveSheet()->setTitle('xx列表');

	// 设置表格宽度
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(5);
	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);


	// 列名表头文字加粗
	$objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFont()->setBold(true);
	// 列表头文字居中
	$objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getAlignment()
		->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

	// 列名赋值
	$objPHPExcel->getActiveSheet()->setCellValue('A1', '会员编号');
	$objPHPExcel->getActiveSheet()->setCellValue('B1', '会员手机号');
	$objPHPExcel->getActiveSheet()->setCellValue('C1', '开户银行');
	$objPHPExcel->getActiveSheet()->setCellValue('D1', '开户帐号');
	$objPHPExcel->getActiveSheet()->setCellValue('E1', '开户姓名');
	$objPHPExcel->getActiveSheet()->setCellValue('F1', '开户地址');
	$objPHPExcel->getActiveSheet()->setCellValue('G1', '提现类型');
	$objPHPExcel->getActiveSheet()->setCellValue('H1', '收款类型');
	$objPHPExcel->getActiveSheet()->setCellValue('I1', '提现金额');
	$objPHPExcel->getActiveSheet()->setCellValue('J1', '实发金额');
	$objPHPExcel->getActiveSheet()->setCellValue('K1', '提现时间');
	$objPHPExcel->getActiveSheet()->setCellValue('L1', '审核状态');
	/*	 $res=db('users')
             ->field('nickname,username,tlevel,mobile,up_userid,center_id,money_jifen,money_baodan,recount,activation,is_lock,reg_time,jihuo_time')
             ->where('id','gt',0)
             ->select();*/
	$str1="";
	$str2="";
	if(!empty($time1)){
		$str1='and tixian_time>'.$time1;
	}
	if(!empty($time2)){
		$str2=' and tixian_time <'.$time2;
	}
	$res=getall("select m.*,u.nickname,u.mobile,u.username,u.bank_name,u.bank_card,u.bank_dizhi from clt_tixian as m join  clt_users as u  on m.userid=u.id where m.id >0 and (u.nickname like '%".$key."%' or u.mobile like '%".$key."%' or u.username like '%".$key."%' ) ".$str1." ".$str2." order by tixian_time desc");


	// 数据起始行
	$row_num = 2;
	// 向每行单元格插入数据
	foreach($res as $value)
	{
		// 设置所有垂直居中
		$objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'J' . $row_num)->getAlignment()
			->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
		// 设置价格为数字格式
		$objPHPExcel->getActiveSheet()->getStyle('D' . $row_num)->getNumberFormat()
			->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
		// 居中
		$objPHPExcel->getActiveSheet()->getStyle('E' . $row_num . ':' . 'H' . $row_num)->getAlignment()
			->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

		// 设置单元格数值
		$objPHPExcel->getActiveSheet()->setCellValue('A' . $row_num, $value['nickname']);
		$objPHPExcel->getActiveSheet()->setCellValue('B' . $row_num, $value['mobile']);
		$objPHPExcel->getActiveSheet()->setCellValue('C' . $row_num, $value['bank_name']);
		$objPHPExcel->getActiveSheet()->setCellValue('D' . $row_num, "'".$value['bank_card']."'");
		$objPHPExcel->getActiveSheet()->setCellValue('E' . $row_num, $value['username']);
		$objPHPExcel->getActiveSheet()->setCellValue('F' . $row_num, $value['bank_dizhi']);

		$name=getone("select `name` from clt_money_config where id=".$value['type']);
		$objPHPExcel->getActiveSheet()->setCellValue('G' . $row_num, $name['name']);
		if($value['shoutype']==1){
			$str="银行卡";
		}elseif($value['shoutype']==2){
			$str= "支付宝";
		}elseif($value['shoutype']==3){
			$str= "微信";
		}
		$objPHPExcel->getActiveSheet()->setCellValue('H' . $row_num,$str);
		$objPHPExcel->getActiveSheet()->setCellValue('I' . $row_num,$value['num']);
		$objPHPExcel->getActiveSheet()->setCellValue('J' . $row_num,$value['shifa_qian']);
		$objPHPExcel->getActiveSheet()->setCellValue('K' . $row_num, date('Y-m-d h:i:s',$value['tixian_time']));
		if ($value['is_ok']==1){
			$zhuangtai= "已通过";
		}elseif($value['is_ok']==2){
			$zhuangtai= "已驳回";
		}else{
			$zhuangtai= "审核中";
		}
		$objPHPExcel->getActiveSheet()->setCellValue('L' . $row_num,$zhuangtai);
		$row_num++;
	}

	$outputFileName = 'teacher_' . time() . '.xls';
	$xlsWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
	ob_end_clean();
	header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
	header("Content-Type: application/force-download");
	header("Content-Type: application/octet-stream");
	header("Content-Type: application/download");
	header('Content-Disposition:inline;filename="' . $outputFileName . '"');
	header("Content-Transfer-Encoding: binary");
	header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
	header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
	header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
	header("Pragma: no-cache");
	$xlsWriter->save("php://output");
	echo file_get_contents($outputFileName);
}
?>



